CREATE VIEW [fin].[eod_options_v] AS
WITH cte (sym_id, sym_option_details)
AS	(	SELECT	  sym_id								AS sym_id
				, CONVERT(  XML
						  , '<Option><Attribute>' + REPLACE(  sym_desc
															, ' '
															, '</Attribute><Attribute>')
							+ '</Attribute></Option>')	AS sym_option_details
		FROM	fin.eod_symbols 
		WHERE	sym_exc_id = 26)
SELECT    cte.sym_id															AS opv_sym_id							
		, sym_option_details.value('/Option[1]/Attribute[1]', 'varchar(25)')	AS opv_sym_underlying_cd 
		, CONVERT(  DATE
		          , REPLACE(  sym_option_details.value('/Option[1]/Attribute[2]'
				            , 'varchar(25)')
							, '-'
							, ' ')
				  , 106)														AS opv_excercise_dt
		, sym_option_details.value('/Option[1]/Attribute[3]', 'varchar(25)')	AS opv_strike
		, sym_option_details.value('/Option[1]/Attribute[4]', 'varchar(25)')	AS opv_option_type   
		, sym.sym_id															AS opv_sym_underlying_id
		, exc.exc_cd															AS opv_exc_underlying_cd
		, exc.exc_id															AS opv_exc_underlying_id
FROM	cte
		INNER JOIN	fin.eod_symbols sym		ON (sym.sym_cd = sym_option_details.value('/Option[1]/Attribute[1]', 'varchar(25)'))
		INNER JOIN	fin.eod_exchanges exc	ON (sym.sym_exc_id = exc.exc_id and exc.exc_cd in ('AMEX', 'NYSE', 'NASDAQ', 'OTCBB'));


